package dk.muldbjergskolen.db;

import java.sql.*;

/**
 * Bruges til at tilgå databasen.
 * 
 * 
 */
public class ConnectionDB {
	// SQL Server
	private static final String driver = "jdbc:sqlserver://balder.ucn.dk";
	private static final String databaseName = ";databaseName=dmaa0912_5";

	private static String userName = ";user=dmaa0912_5";
	private static String password = ";password=MaaGodt";

	private DatabaseMetaData dma;
	private static Connection con;

	private static ConnectionDB instance = null;

	/**
	 * Kun én forbindelse er tilladt.
	 */
	private ConnectionDB() {
		String url = driver + databaseName + userName + password;

		try {
			// Det eksterne bibliotek er nødvendigt for at tilgå databasen
			// (sqljdbc4.jar).
			// Tjek om det er tilgængeligt.
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			System.out.println("Load af class ok");
		} catch (Exception e) {
			System.out.println("Can not find the driver");
			System.out.println(e.getMessage());
		}

		try {
			// connection to the database
			con = DriverManager.getConnection(url);
			// set autocommit
			con.setAutoCommit(true);
			dma = con.getMetaData(); // get meta data
			System.out.println("Connection to " + dma.getURL());
			System.out.println("Driver " + dma.getDriverName());
			System.out.println("Database product name "
					+ dma.getDatabaseProductName());
		} catch (Exception e) {
			System.out.println("Problems with the connection to the database");
			System.out.println(e.getMessage());
			System.out.println(url);
		}
	}

	/**
	 * Lukker forbindelsen til databasen.
	 */
	public static void closeConnection() {
		try {
			con.close();
			System.out.println("The connection is closed");
		} catch (Exception e) {
			System.out.println("Error trying to close the database "
					+ e.getMessage());
		}
	}

	/**
	 * Returnerer selve forbindelsen.
	 * 
	 * @return en connection.
	 */
	public Connection getDBcon() {
		return con;
	}

	/**
	 * Få fat i databasen.
	 * 
	 * @return ConnectionDB.
	 */
	public static ConnectionDB getInstance() {
		if (instance == null) {
			instance = new ConnectionDB();
		}
		return instance;
	}

	/**
	 * Starter en transaktion.
	 */
	public static void startTransaction() {
		try {
			con.setAutoCommit(false);
		} catch (Exception e) {
			System.out.println("fejl start transaction");
			System.out.println(e.getMessage());
		}
	}

	/**
	 * Afslutter en transaktion.
	 */
	public static void commitTransaction() {
		try {
			con.setAutoCommit(true);
		} catch (Exception e) {
			System.out.println("fejl commit transaction");
			System.out.println(e.getMessage());
		}
	}

	/**
	 * Fortryder en transaktion.
	 */
	public static void rollbackTransaction() {
		try {
			con.rollback();
			con.setAutoCommit(true);
		} catch (Exception e) {
			System.out.println("fejl rollback transaction");
			System.out.println(e.getMessage());
		}
	}

	/**
	 * Henter det største id i en tabel ud fra en sql string.
	 * 
	 * @param tabel
	 *            hvor id'et på den nyeste post skal findes.
	 * @return det højeste nummer på en post i en tabel i forhold til query.
	 */
	public int getMaxId(String tabel) {
		int id = 0;
		try {
			Statement stmt = con.createStatement();
			ResultSet results = stmt.executeQuery("SELECT IDENT_CURRENT('"
					+ tabel + "')");
			if (results.next()) {
				id = results.getInt(1);
			}
		} catch (Exception e) {
			System.out.println("getMaxId: Fejl ved læsning af maxId: " + e);
		}
		return id;
	}

	/**
	 * Returnerer et ResultSet for en given query.
	 * 
	 * @param query
	 * @return
	 * @throws SQLException
	 */
	public ResultSet getResultSet(String query) throws SQLException {
		ResultSet rs = null;
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rs = stmt.executeQuery(query);

			// System.out.println("Udført: " + query); Det noget værre spam.
		} catch (SQLException e) {
			System.out.println("FEJLEDE: " + query);
			// System.out.println(e.getMessage());

			throw e;
		}

		return rs;
	}

	/**
	 * Eksekverer en query.
	 * 
	 * @param query
	 * @throws SQLException
	 */
	public void execQuery(String query) throws SQLException {
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			stmt.executeUpdate(query);

			// System.out.println("Udført: " + query);
		} catch (SQLException e) {
			System.out.println("FEJLEDE: " + query);
			// System.out.println(e.getMessage());

			// Send fejlen videre (hvis metoden bliver brugt i en transaktion).
			throw e;
		}
	}
}
